package com.rickpan.repository;

import com.rickpan.entity.Team;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

/**
 * 团队数据访问层
 */
@Repository
public interface TeamRepository extends JpaRepository<Team, Long> {

    /**
     * 根据创建者ID查找团队
     */
    List<Team> findByCreatorId(Long creatorId);

    /**
     * 根据状态查找团队
     */
    List<Team> findByStatus(Team.Status status);

    /**
     * 根据创建者ID和状态查找团队
     */
    List<Team> findByCreatorIdAndStatus(Long creatorId, Team.Status status);

    /**
     * 分页查询团队
     */
    Page<Team> findByStatus(Team.Status status, Pageable pageable);

    /**
     * 根据名称模糊查询团队
     */
    @Query("SELECT t FROM Team t WHERE t.name LIKE %:name% AND t.status = :status")
    List<Team> findByNameContainingAndStatus(@Param("name") String name, @Param("status") Team.Status status);

    /**
     * 查询用户参与的所有团队
     */
    @Query("SELECT DISTINCT t FROM Team t " +
           "JOIN TeamMember tm ON t.id = tm.teamId " +
           "WHERE tm.userId = :userId AND tm.status = 'ACTIVE' AND t.status IN ('PENDING', 'APPROVED', 'ACTIVE')")
    List<Team> findTeamsByUserId(@Param("userId") Long userId);

    /**
     * 查询用户管理的团队
     */
    @Query("SELECT DISTINCT t FROM Team t " +
           "JOIN TeamMember tm ON t.id = tm.teamId " +
           "WHERE tm.userId = :userId AND tm.role IN ('OWNER', 'ADMIN') AND tm.status = 'ACTIVE' AND t.status IN ('APPROVED', 'ACTIVE')")
    List<Team> findManagedTeamsByUserId(@Param("userId") Long userId);

    /**
     * 查询用户拥有的团队
     */
    @Query("SELECT DISTINCT t FROM Team t " +
           "JOIN TeamMember tm ON t.id = tm.teamId " +
           "WHERE tm.userId = :userId AND tm.role = 'OWNER' AND tm.status = 'ACTIVE'")
    List<Team> findOwnedTeamsByUserId(@Param("userId") Long userId);

    /**
     * 统计待审核的团队数量
     */
    @Query("SELECT COUNT(t) FROM Team t WHERE t.status = 'PENDING'")
    long countPendingTeams();

    /**
     * 统计已批准的团队数量
     */
    @Query("SELECT COUNT(t) FROM Team t WHERE t.status = 'APPROVED'")
    long countApprovedTeams();

    /**
     * 统计用户创建的团队数量
     */
    long countByCreatorId(Long creatorId);

    /**
     * 统计用户创建的已批准团队数量
     */
    long countByCreatorIdAndStatus(Long creatorId, Team.Status status);

    /**
     * 检查团队名称是否已存在
     */
    boolean existsByName(String name);

    /**
     * 检查用户是否已创建同名团队
     */
    boolean existsByCreatorIdAndName(Long creatorId, String name);

    /**
     * 查找最近创建的团队
     */
    @Query("SELECT t FROM Team t WHERE t.status = 'APPROVED' ORDER BY t.createdAt DESC")
    List<Team> findRecentTeams(Pageable pageable);

    /**
     * 查找活跃团队（有活跃成员的团队）
     */
    @Query("SELECT DISTINCT t FROM Team t " +
           "JOIN TeamMember tm ON t.id = tm.teamId " +
           "WHERE tm.status = 'ACTIVE' AND t.status = 'APPROVED' " +
           "GROUP BY t.id " +
           "HAVING COUNT(tm.id) > 0 " +
           "ORDER BY t.updatedAt DESC")
    List<Team> findActiveTeams(Pageable pageable);

    /**
     * 根据成员数量排序查找团队
     */
    @Query("SELECT t FROM Team t WHERE t.status = 'APPROVED' ORDER BY t.currentMembers DESC")
    List<Team> findTeamsOrderByMemberCount(Pageable pageable);

    /**
     * 查找需要审核的团队（管理员用）
     */
    @Query("SELECT t FROM Team t WHERE t.status = 'PENDING' ORDER BY t.createdAt ASC")
    List<Team> findPendingTeamsForReview(Pageable pageable);

    /**
     * 全文搜索团队
     */
    @Query("SELECT t FROM Team t WHERE " +
           "(t.name LIKE %:keyword% OR t.description LIKE %:keyword%) " +
           "AND t.status = 'APPROVED'")
    List<Team> searchTeams(@Param("keyword") String keyword, Pageable pageable);

    /**
     * 根据可见性查找团队
     */
    Page<Team> findByVisibility(Team.Visibility visibility, Pageable pageable);

    /**
     * 根据可见性和状态查找团队
     */
    @Query("SELECT t FROM Team t WHERE t.visibility = :visibility AND t.status = :status")
    List<Team> findByVisibilityAndStatus(@Param("visibility") Team.Visibility visibility,
                                        @Param("status") Team.Status status);

    /**
     * 查找对用户可见的团队
     */
    @Query("SELECT DISTINCT t FROM Team t " +
           "LEFT JOIN TeamMember tm ON t.id = tm.teamId AND tm.userId = :userId AND tm.status = 'ACTIVE' " +
           "WHERE (t.visibility = 'PUBLIC') " +
           "OR (t.visibility = 'INTERNAL' AND :userId IS NOT NULL) " +
           "OR (t.visibility = 'PRIVATE' AND tm.id IS NOT NULL) " +
           "AND t.status = 'APPROVED'")
    List<Team> findVisibleTeamsForUser(@Param("userId") Long userId, Pageable pageable);
}
